package com.mustafaiev.tair.mweather.database;

import java.util.ArrayList;
import java.util.HashMap;

import android.app.SearchManager;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.database.sqlite.SQLiteQueryBuilder;
import android.provider.BaseColumns;
import android.util.Log;

import com.mustafaiev.tair.mweather.MWeatherCommonHelper;
import com.mustafaiev.tair.mweather.exception.MWeatherParseException;
import com.mustafaiev.tair.mweather.forecast.Town;
import com.mustafaiev.tair.mweather.xml.MWeatherXMLParser;

/**
 * 
 * Class creates/opens database. Creation date: 3 Sep 2011 18:00:45
 * 
 * @author Tair Mustafaiev
 * 
 */
public class MWeatherDBHelper extends SQLiteOpenHelper {

	private Context context;
	private SQLiteDatabase sqLiteDatabase;

	private final static String TAG = "MWeatherDBHelper, method ";

	// database name
	public static final String MWEATHER_DB_NAME = "MWeather";
	// database version
	private static final int MWEATHER_DATABASE_VERSION = 2;
	// table name
	public static final String MWEATHER_TOWNS_TABLE_NAME = "Towns";
	// columns
	public static final String MWEATHER_COLUMN_ID = BaseColumns._ID;
	public static final String MWEATHER_COLUMN_TOWN_NAME = SearchManager.SUGGEST_COLUMN_TEXT_1;
	public static final String MWEATHER_COLUMN_TOWN_OLD_ID = SearchManager.SUGGEST_COLUMN_TEXT_2;
	public static final String MWEATHER_COLUMN_TOWN_NEW_ID = "TownNewId";

	private static final String QUERY_FTS3_TABLE_CREATE = "CREATE VIRTUAL TABLE "
			+ MWeatherDBHelper.MWEATHER_TOWNS_TABLE_NAME
			+ " USING fts3 ("
			+ MWeatherDBHelper.MWEATHER_COLUMN_ID
			+ ", "
			+ MWeatherDBHelper.MWEATHER_COLUMN_TOWN_NAME
			+ ", "
			+ MWeatherDBHelper.MWEATHER_COLUMN_TOWN_NEW_ID
			+ ", "
			+ MWeatherDBHelper.MWEATHER_COLUMN_TOWN_OLD_ID + ");";

	private final HashMap<String, String> columnsMap = MWeatherDBHelper
			.buildColumnMap();

	/**
	 * Default constructor. Creation date: 3 Sep 2011 18:01:29
	 * 
	 * @param context
	 * @param name
	 * @param factory
	 * @param version
	 */
	public MWeatherDBHelper(Context context) {
		super(context, MWeatherDBHelper.MWEATHER_DB_NAME, null,
				MWeatherDBHelper.MWEATHER_DATABASE_VERSION);
		this.setContext(context);
	}

	/**
	 * @return the context
	 */
	public Context getContext() {
		return this.context;
	}

	/**
	 * @param context
	 *            the context to set
	 */
	public void setContext(Context context) {
		this.context = context;
	}

	/**
	 * Builds a map for all columns that may be requested, which will be given
	 * to the SQLiteQueryBuilder. This is a good way to define aliases for
	 * column names, but must include all columns, even if the value is the key.
	 * This allows the ContentProvider to request columns w/o the need to know
	 * real column names and create the alias itself. Creation date: 4 Sep 2011
	 * 01:44:14
	 * 
	 * @return map of columns
	 */
	private static HashMap<String, String> buildColumnMap() {
		HashMap<String, String> map = new HashMap<String, String>();

		map.put(MWeatherDBHelper.MWEATHER_COLUMN_TOWN_NAME,
				MWeatherDBHelper.MWEATHER_COLUMN_TOWN_NAME);
		map.put(MWeatherDBHelper.MWEATHER_COLUMN_TOWN_NEW_ID,
				MWeatherDBHelper.MWEATHER_COLUMN_TOWN_NEW_ID);
		map.put(MWeatherDBHelper.MWEATHER_COLUMN_TOWN_OLD_ID,
				MWeatherDBHelper.MWEATHER_COLUMN_TOWN_OLD_ID);

		map.put(BaseColumns._ID, "rowid AS " + BaseColumns._ID);
		map.put(SearchManager.SUGGEST_COLUMN_INTENT_DATA_ID, "rowid AS "
				+ SearchManager.SUGGEST_COLUMN_INTENT_DATA_ID);
		map.put(SearchManager.SUGGEST_COLUMN_SHORTCUT_ID, "rowid AS "
				+ SearchManager.SUGGEST_COLUMN_SHORTCUT_ID);

		return map;
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		this.sqLiteDatabase = db;
		Log.i(MWeatherDBHelper.TAG + "onCreate()", "Table Creation start");
		this.sqLiteDatabase.execSQL(MWeatherDBHelper.QUERY_FTS3_TABLE_CREATE);
		Log.i(MWeatherDBHelper.TAG + "onCreate()", "Table Creation end");
		Log.i(MWeatherDBHelper.TAG + "onCreate()", "Data insert");
		this.insertData();

	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		db.execSQL("DROP TABLE IF EXISTS "
				+ MWeatherDBHelper.MWEATHER_TOWNS_TABLE_NAME);
		this.onCreate(db);
	}

	/**
	 * Creation date: 4 Sep 2011 01:56:54
	 * 
	 * @param rowId
	 * @param columns
	 * @return {@link Cursor} result
	 */
	public Cursor getSingleWord(String rowId, String[] columns) {

		String selection = MWeatherDBHelper.MWEATHER_COLUMN_TOWN_NAME + " = ?";
		String[] selectionArgs = { rowId };

		return this.query(columns, selection, selectionArgs);
	}

	/**
	 * Returns a Cursor over all words that match the given query. Creation
	 * date: 4 Sep 2011 01:52:49
	 * 
	 * @param query
	 *            The string to search for
	 * @param columns
	 *            The columns to include, if null then all are included
	 * @return Cursor over all words that match, or null if none found.
	 */
	public Cursor getWordMatches(String query, String[] columns) {
		String selection = MWeatherDBHelper.MWEATHER_COLUMN_TOWN_NAME
				+ " MATCH ?";
		String[] selectionArgs = new String[] { query + "*" };

		return this.query(columns, selection, selectionArgs);
	}

	/**
	 * Creation date: 4 Sep 2011 01:51:43
	 * 
	 * @param columns
	 * @param selection
	 * @param selectionArgs
	 * @return {@link Cursor} result
	 */
	private Cursor query(String[] columns, String selection,
			String[] selectionArgs) {

		SQLiteQueryBuilder builder = new SQLiteQueryBuilder();
		builder.setTables(MWeatherDBHelper.MWEATHER_TOWNS_TABLE_NAME);
		builder.setProjectionMap(this.columnsMap);

		Cursor cursor = builder.query(this.getReadableDatabase(), columns,
				selection, selectionArgs, null, null, null);

		if (cursor == null) {
			return null;
		} else if (!cursor.moveToFirst()) {
			cursor.close();
			return null;
		}
		return cursor;
	}

	/**
	 * Method inserts rows into database. Creation date: 3 Sep 2011 19:52:55
	 */
	private void insertData() {
		new Thread(new Runnable() {
			public void run() {
				Log.i(MWeatherDBHelper.TAG + "insertData()",
						"Data inserting start");
				MWeatherDBHelper.this.insertRows();
				Log.i(MWeatherDBHelper.TAG + "insertData()",
						"Data inserting end");
			}
		}).start();

	}

	/**
	 * Method loads and returns town list. Creation date: 3 Sep 2011 19:25:33
	 * 
	 * @return {@link ArrayList} of towns
	 */
	private ArrayList<Town> loadTownList() {
		MWeatherXMLParser parser = new MWeatherXMLParser();
		ArrayList<Town> towns = null;
		try {
			towns = parser.getParsedTowns(this.getContext().getAssets());
		} catch (MWeatherParseException e) {
			Log.e(MWeatherDBHelper.TAG + "loadTownList()", e.toString());
		}
		return towns;
	}

	/**
	 * Adds rows into database. Creation date: 3 Sep 2011 20:13:31
	 * 
	 */
	private void insertRows() {

		ArrayList<Town> towns = this.loadTownList();
		ContentValues values = new ContentValues();
		int id = 1;
		for (Town town : towns) {
			long rowInserted = this.insertRow(values, town, id);
			Log.i(MWeatherDBHelper.TAG + "insertRows()", "Row with id: " + id
					+ " inserted - " + rowInserted);
			if (rowInserted < 0) {
				Log.e(MWeatherDBHelper.TAG + "insertRows()", "Row with id "
						+ id + "was not inserted!");
				throw new UnsupportedOperationException(MWeatherDBHelper.TAG
						+ "insertRows() " + " Data creation impossible!");
			}

			id++;
		}
	}

	/**
	 * Adds row into database. Creation date: 3 Sep 2011 20:20:12
	 * 
	 * @param values
	 * @param town
	 * @param id
	 * @return
	 */
	private long insertRow(ContentValues values, Town town, int id) {

		String townName = MWeatherCommonHelper.translitWord(town.getTownName());
		values.put(MWeatherDBHelper.MWEATHER_COLUMN_ID, id);
		values.put(MWeatherDBHelper.MWEATHER_COLUMN_TOWN_NAME, townName);
		values.put(MWeatherDBHelper.MWEATHER_COLUMN_TOWN_NEW_ID,
				town.getTownNewId());
		values.put(MWeatherDBHelper.MWEATHER_COLUMN_TOWN_OLD_ID,
				town.getTownOldId());
		return this.sqLiteDatabase.insert(
				MWeatherDBHelper.MWEATHER_TOWNS_TABLE_NAME, null, values);

	}
}
